﻿
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace Interactive.Utility.Sql {
    /// <summary>
	/// SqlHelper 提供一次性开闭数据库访问请求的静态方法。
	/// </summary>
	public class SqlHelper : IDisposable {
		/// <summary>
		/// 默认的数据库连接名称
		/// </summary>
		public const string CONN_STRING_NAME = "default";

		private bool keepOpen;


		/// <summary>
		/// 根据连接名称创建SqlConnection
		/// </summary>
		/// <param name="connectionStringName">连接名称（注意：不是连接字符串）</param>
		public SqlHelper(string connectionStringName) {
			ConnectionStringSettings connectionSettings = ConfigurationManager.ConnectionStrings[connectionStringName];
			if (connectionSettings == null)
				return;
			string connstr = connectionSettings.ConnectionString;
			if (!string.IsNullOrEmpty(connstr))
				Connection = new SqlConnection(ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString);
		}

		/// <summary>
		/// 使用默认连接名称"default"创建SqlConnection
		/// </summary>
		[Obsolete("请使用以连接名称作为参数的构造函数类型。多个部署于相同虚拟应用均使用默认构造函数可能导致冲突。")]
		public SqlHelper() : this(CONN_STRING_NAME) { }

		/// <summary>
		/// 当前数据库连接
		/// </summary>
        public SqlConnection Connection { get; private set; }

		/// <summary>
		/// 默认为False，执行完方法后自动关闭连接
		/// 设置时为true时打开连接；false时关闭连接
		/// </summary>
		public bool KeepOpen {
			get {
				return keepOpen;
			}
			set {
				if (Connection == null)
					return;

				keepOpen = value;
				if (value && Connection.State == ConnectionState.Closed)
					Connection.Open();
				else if (!value && Connection.State == ConnectionState.Open)
					Connection.Close();
			}
		}

		#region 过期方法

		/// <summary>
		/// 返回一个SqlDataReader实例，配置为空时返回Null
		/// </summary>
		/// <param name="cmd">查询命令</param>
		/// <returns></returns>
		[Obsolete("临时屏蔽链接时会抛出异常，建议改用CreateReader方法")]
		public SqlDataReader ExecuteReader(SqlCommand cmd) {
			if (Connection == null)
				return null;

			cmd.Connection = Connection;
			if (Connection.State == ConnectionState.Closed)
				cmd.Connection.Open();
			if (keepOpen)
				return cmd.ExecuteReader();
			return cmd.ExecuteReader(CommandBehavior.CloseConnection);
		}

		/// <summary>
		/// 返回一个SqlDataReader实例，配置为空时返回Null
		/// </summary>
		/// <param name="commandText">查询命令</param>
		/// <returns></returns>
		[Obsolete("临时屏蔽链接时会抛出异常，建议改用CreateReader方法")]
		public SqlDataReader ExecuteReader(string commandText) { return CreateReader(new SqlCommand(commandText)) as SqlDataReader; }

		#endregion

		#region 核心方法

		/// <summary>
		/// 返回一个DbDataReader实例。当链接配置为空时返回一个DummyDBReader
		/// </summary>
		public DbDataReader CreateReader(SqlCommand cmd) {
			if (Connection == null)
				return DummyDataReader.Instance;
			cmd.Connection = Connection;
			if (Connection.State == ConnectionState.Closed)
				cmd.Connection.Open();
			try {
				return keepOpen ? cmd.ExecuteReader() : cmd.ExecuteReader(CommandBehavior.CloseConnection);
			} catch (SqlException e) {
				if (e.Number == -2)
					throw new QueryTimeoutException();
				throw;
			}
		}

		/// <summary>
		/// ExcuteScalar()方法
		/// </summary>
		/// <param name="cmd">查询命令</param>
		/// <returns></returns>
		public object ExecuteScalar(SqlCommand cmd) {
			if (Connection == null)
				return null;

			object o;
			cmd.Connection = Connection;
			try {
				if (Connection.State == ConnectionState.Closed)
					cmd.Connection.Open();
				o = cmd.ExecuteScalar();
			} catch (SqlException e) {
				if (e.Number == -2)
					throw new QueryTimeoutException();
				throw;
			} finally {
				if (!keepOpen)
					Connection.Close();
			}
			return o;
		}

		/// <summary>
		/// ExecuteNonQuery()方法
		/// </summary>
		/// <param name="cmd">查询命令</param>
		/// <returns></returns>
		public int ExecuteNonQuery(SqlCommand cmd) {
			if (Connection == null)
				return -1;

			cmd.Connection = Connection;
			try {
				if (Connection.State == ConnectionState.Closed)
					cmd.Connection.Open();
				return cmd.ExecuteNonQuery();
			} catch (SqlException e) {
				if (e.Number == -2)
					throw new QueryTimeoutException();
				throw;
			} finally {
				if (!keepOpen)
					Connection.Close();
			}
		}


		/// <summary>
		/// 返回一个保存查询结果的IDictionary
		/// </summary>
		/// <param name="cmd">查询命令</param>
		/// <returns></returns>
		public IDictionary ExecuteRow(SqlCommand cmd) {
			if (Connection == null)
				return null;

			IDictionary row;
			cmd.Connection = Connection;
			try {
				if (Connection.State == ConnectionState.Closed)
					cmd.Connection.Open();
				using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow)) {
					if (dr != null && dr.Read()) {
						if (dr.FieldCount < 8)
							row = new HybridDictionary(dr.FieldCount);
						else
							row = new Dictionary<string, object>(dr.FieldCount);
						for (int i = 0; i < dr.FieldCount; i++)
							row.Add(dr.GetName(i), dr.GetValue(i));
						return row;
					}
					return null;
				}
			} catch (SqlException e) {
				if (e.Number == -2)
					throw new QueryTimeoutException();
				throw;
			} finally {
				if (!keepOpen)
					Connection.Close();
			}
		}

		#endregion

		#region 重载方法

		/// <summary>
		/// Descriptiont:返回一个DbDataReader实例。当链接配置为空时返回一个DummyDBReader
		/// Author:余观成
		/// Date:2008/7/3
		/// </summary>
		/// <param name="storedProcedure">存储过程</param>
		/// <param name="parameters">参数数组</param>
		/// <returns></returns>
		public DbDataReader CreateReader(string storedProcedure, params SqlParameter[] parameters) {
			SqlCommand cmd = new SqlCommand {CommandType = CommandType.StoredProcedure, CommandText = storedProcedure, Connection = Connection};
			if (parameters != null) {
				for (int i = 0; i < parameters.Length; i++)
					cmd.Parameters.Add(parameters[i]);
			}
			return CreateReader(cmd);
		}

		/// <summary>
		/// 返回一个DbDataReader实例。当链接配置为空时返回一个DummyDBReader
		/// </summary>
		public DbDataReader CreateReader(string commandText) { return CreateReader(new SqlCommand(commandText)); }

		/// <summary>
		/// Description:返回查询第一行第一列的值
		/// Author:余观成
		/// Date:2008/7/3
		/// </summary>
		/// <param name="storedProcedure">存储过程</param>
		/// <param name="parameters">参数数组</param>
		/// <returns></returns>
		public object ExecuteScalar(string storedProcedure, params SqlParameter[] parameters) {
			SqlCommand cmd = new SqlCommand {CommandType = CommandType.StoredProcedure, CommandText = storedProcedure, Connection = Connection};
			if (parameters != null) {
				for (int i = 0; i < parameters.Length; i++)
					cmd.Parameters.Add(parameters[i]);
			}
			return ExecuteScalar(cmd);
		}

		/// <summary>
		/// ExcuteScalar()方法
		/// </summary>
		/// <param name="commandText">SQL命令行</param>
		/// <returns></returns>
		public object ExecuteScalar(string commandText) { return ExecuteScalar(new SqlCommand(commandText)); }

		/// <summary>
		/// ExecuteNonQuery()方法
		/// </summary>
		/// <param name="commandText">SQL命令行</param>
		/// <returns></returns>
		public int ExecuteNonQuery(string commandText) { return ExecuteNonQuery(new SqlCommand(commandText)); }

		/// <summary>
		/// Descriptiont:执行一个存储过程，后带参数
		/// Author:余观成
		/// Date:2008/7/3
		/// </summary>
		/// <param name="storedProcedure">存储过程</param>
		/// <param name="parameters">参数数组</param>
		/// <returns>返回受影响的行数</returns>
		public int ExecuteNonQuery(string storedProcedure, params SqlParameter[] parameters) {
			SqlCommand cmd = new SqlCommand {CommandType = CommandType.StoredProcedure, CommandText = storedProcedure, Connection = Connection};
			if (parameters != null) {
				for (int i = 0; i < parameters.Length; i++)
					cmd.Parameters.Add(parameters[i]);
			}
			return ExecuteNonQuery(cmd);
		}

		/// <summary>
		/// 返回一个保存查询结果的IDictionary
		/// </summary>
		/// <param name="commandText">SQL命令行</param>
		/// <returns></returns>
		public IDictionary ExecuteRow(string commandText) { return ExecuteRow(new SqlCommand(commandText)); }


		/// <summary>
		/// Descriptiont:返回一个保存查询结果的IDictionary
		/// Author:余观成
		/// Date:2008/7/3
		/// </summary>
		/// <param name="storedProcedure">存储过程</param>
		/// <param name="parameters">参数数组</param>
		/// <returns></returns>
		public IDictionary ExecuteRow(string storedProcedure, params SqlParameter[] parameters) {
			SqlCommand cmd = new SqlCommand {CommandType = CommandType.StoredProcedure, CommandText = storedProcedure, Connection = Connection};
			if (parameters != null) {
				for (int i = 0; i < parameters.Length; i++)
					cmd.Parameters.Add(parameters[i]);
			}
			return ExecuteRow(cmd);
		}

		#endregion

		#region 屏蔽的方法

		/// <summary>
		/// 将查询的第一个结果集作为DataTable返回
		/// 注意：性能较低的实现方式。不建议使用
		/// </summary>
		/// <param name="cmd">查询命令</param>
		/// <returns></returns>
		[EditorBrowsable(EditorBrowsableState.Never)]
		public DataTable ExecuteDataTable(SqlCommand cmd) {
			if (Connection == null)
				return null;

			cmd.Connection = Connection;
			try {
				DataTable dt = new DataTable();
				using (IDataReader dr = CreateReader(cmd))
					dt.Load(dr);
				return dt;
			} finally {
				if (!keepOpen)
					Connection.Close();
			}
		}

		/// <summary>
		/// Descriptiont:将查询的第一个结果集作为DataTable返回
		/// 注意：性能较低的实现方式。不建议使用
		/// Author:余观成
		/// Date:2008/7/3
		/// </summary>
		/// <param name="storedProcedure">存储过程</param>
		/// <param name="parameters">参数数组</param>
		/// <returns></returns>
		[EditorBrowsable(EditorBrowsableState.Never)]
		public DataTable ExecuteDataTable(string storedProcedure, params SqlParameter[] parameters) {
			SqlCommand cmd = new SqlCommand {CommandType = CommandType.StoredProcedure, CommandText = storedProcedure, Connection = Connection};
			if (parameters != null) {
				for (int i = 0; i < parameters.Length; i++)
					cmd.Parameters.Add(parameters[i]);
			}
			return ExecuteDataTable(cmd);
		}

		/// <summary>
		/// 将查询的第一个结果集作为DataTable返回
		/// 注意：性能较低的实现方式。不建议使用
		/// </summary>
		/// <param name="commandText">查询命令文本</param>
		/// <returns></returns>
		[EditorBrowsable(EditorBrowsableState.Never)]
		public DataTable ExecuteDataTable(string commandText) { return ExecuteDataTable(new SqlCommand(commandText)); }

		/// <summary>
		/// 返回一个DataSet实例。
		/// 注意：性能较低的实现方式。不建议使用
		/// </summary>
		/// <param name="cmd">查询命令</param>
		/// <returns></returns>
		[EditorBrowsable(EditorBrowsableState.Never)]
		public DataSet ExecuteDataSet(SqlCommand cmd) {
			if (Connection == null)
				return null;

			cmd.Connection = Connection;
			try {
				SqlDataAdapter da = new SqlDataAdapter(cmd);
				DataSet ds = new DataSet();
				da.Fill(ds);
				return ds;
			} finally {
				if (!keepOpen)
					Connection.Close();
			}
		}

		/// <summary>
		/// Descriptiont:将查询的第一个结果集作为DataSet返回
		/// 注意：性能较低的实现方式。不建议使用
		/// Author:余观成
		/// Date:2008/7/3
		/// </summary>
		/// <param name="storedProcedure">存储过程</param>
		/// <param name="parameters">参数数组</param>
		/// <returns></returns>
		[EditorBrowsable(EditorBrowsableState.Never)]
		public DataSet ExecuteDataSet(string storedProcedure, params SqlParameter[] parameters) {
			SqlCommand cmd = new SqlCommand {CommandType = CommandType.StoredProcedure, CommandText = storedProcedure, Connection = Connection};
			if (parameters != null) {
				for (int i = 0; i < parameters.Length; i++)
					cmd.Parameters.Add(parameters[i]);
			}
			return ExecuteDataSet(cmd);
		}

		/// <summary>
		/// 返回一个DataSet实例。
		/// 注意：性能较低的实现方式。不建议使用
		/// </summary>
		/// <param name="commandText">SQL命令行</param>
		/// <returns></returns>
		[EditorBrowsable(EditorBrowsableState.Never)]
		public DataSet ExecuteDataSet(string commandText) { return ExecuteDataSet(new SqlCommand(commandText)); }

		#endregion

		#region IDisposable 成员

		/// <summary>
		/// 释放资源。一般用于KeepOpen=true
		/// </summary>
		public void Dispose() { Connection.Dispose(); }

		#endregion

		#region 其它转换函数

		/// <summary>
		/// 从SqlDbType到TypeCode的转换函数
		/// </summary>
		/// <param name="sqlDbType"></param>
		/// <returns></returns>
		public static TypeCode SqlDBTypeToTypeCode(SqlDbType sqlDbType) {
			TypeCode typeCode;
			switch (sqlDbType) {
				case SqlDbType.BigInt:
					typeCode = TypeCode.Int64;
					break;
				case SqlDbType.Bit:
					typeCode = TypeCode.Boolean;
					break;
				case SqlDbType.Char:
					typeCode = TypeCode.String;
					break;
				case SqlDbType.DateTime:
					typeCode = TypeCode.DateTime;
					break;
				case SqlDbType.Decimal:
					typeCode = TypeCode.Decimal;
					break;
				case SqlDbType.Float:
					typeCode = TypeCode.Double;
					break;
				case SqlDbType.Int:
					typeCode = TypeCode.Int32;
					break;
				case SqlDbType.Money:
					typeCode = TypeCode.Decimal;
					break;
				case SqlDbType.NChar:
					typeCode = TypeCode.String;
					break;
				case SqlDbType.NText:
					typeCode = TypeCode.String;
					break;
				case SqlDbType.NVarChar:
					typeCode = TypeCode.String;
					break;
				case SqlDbType.Real:
					typeCode = TypeCode.Single;
					break;
				case SqlDbType.SmallDateTime:
					typeCode = TypeCode.DateTime;
					break;
				case SqlDbType.SmallInt:
					typeCode = TypeCode.Int16;
					break;
				case SqlDbType.SmallMoney:
					typeCode = TypeCode.Decimal;
					break;
				case SqlDbType.Text:
					typeCode = TypeCode.String;
					break;
				case SqlDbType.TinyInt:
					typeCode = TypeCode.SByte;
					break;
				case SqlDbType.VarChar:
					typeCode = TypeCode.Single;
					break;
				default:
					typeCode = TypeCode.Object;
					break;
			}
			return typeCode;
		}

		#endregion

		/// <summary>
		/// 将传入参数尝试转化为指定类型。转换失败返回空
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="obj"></param>
		/// <returns></returns>
		public static T? GetNullableValue<T>(object obj) where T:struct {
			return obj is T ? (T)obj : new T?();
		}

		/// <summary>
		/// 返回一个执行存储过程的SqlCommand实例
		/// </summary>
		/// <param name="name"></param>
		/// <returns></returns>
		public static SqlCommand CreateSpCommand(string name) {
			return new SqlCommand(name) {CommandType = CommandType.StoredProcedure};
		}
	}
}